<?php
namespace Admin\Controller;
use Think\Controller;
use Think\Exception;
use Think\Model;

/**
 * 店铺订单统计
 * @package Admin\Controller
 */
class TaskTongJiController extends Controller{

    /**
     * 订单统计
     */

    public function orderSale(){

        try {
            //支付流水统计
            $res =  M()->query("SELECT SUM(b.money) AS order_total,COUNT(*) AS order_count,avg(b.money) AS order_one,ls.local_id FROM sdb_webpos_local_store ls LEFT JOIN `sdb_b2c_bills` b  on b.local_id = ls.local_id WHERE bill_type = 'payments' GROUP BY ls.local_id");

            if($res === false){throw new Exception('查询数据异常');}
            if(!empty($res)){
                foreach ($res as $k=>$val){
                    $is_found = M('statistical_local_order')->where(['local_id'=>$val['local_id']])->find();

                    if(!empty($is_found)){
                        $save_1 = M('statistical_local_order')->where(['local_id'=>$val['local_id']])->save($val);
                        if($save_1===false){
                            throw new Exception('门店ID:'.$val['local_id'].'订单统计更新失败');
                        }
                    }else{
                        $save_2 = M('statistical_local_order')->add($val);
                        if($save_2===false){
                            throw new Exception('门店ID'.$val['local_id'].'订单统计添加失败');
                        }
                    }
                }
            }
            $log = ['name'=>'门店订单统计更新','log_status'=>'1','run_msg'=>'正常完成','run_time'=>time()];
            M('statistical_log')->add($log);

        }catch (Exception $e){
            $log = ['name'=>'门店订单统计更新','log_status'=>'2','run_msg'=>$e->getMessage(),'run_time'=>time()];
            M('statistical_log')->add($log);
        }



    }
    //订单按天订单数量统计
    public function orderDayList(){
        $time = date("Y-m-d",strtotime("-1 day"))." 23:59:59";//昨天凌晨结束
        $time = strtotime($time);
        $time_start = date("Y-m-d",strtotime("-1 day"))." 0:0:0";//昨天开始
        $time_start = strtotime($time_start);


        //查询是否已经插入过昨天订单统计记录
        $time_1 = date("Y-m-d",strtotime("-1 day"))." 0:0:0";

        try{

            $is_add = M('statistical_order_day')->where(['time'=>$time_1])->find();
            if(!empty($is_add)){

              $dele_res = M('statistical_order_day')->where(['time'=>$time_1])->delete();
              if($dele_res === false){
                  throw new Exception('已经添加昨日的统计记录,删除上次记录失败');
              }

            }

            $rest =  M('statistical_order_day')->select();
            if(!empty($rest)){
                $where = "and b.time >= ".$time_start." and b.time < ".$time;
            }else{
                $where = "and b.time < ".$time;
            }

            //按天统计订单
            $res = M()->query("SELECT FROM_UNIXTIME(b.time, \"%Y-%m-%d\") AS day,b.local_id,count(*) AS order_time,sum(b.money) AS money,b.local_id as local_id,sum(o.itemnum) AS goods_num FROM sdb_b2c_bills b LEFT JOIN sdb_b2c_orders o ON o.order_id = b.order_id WHERE b.bill_type = 'payments' $where GROUP BY day,b.local_id");
            if(!empty($res)){
                foreach ($res as &$v){
                    $v['created_at'] = date('Y-m-d H:i:s',time());
                    $v['time'] = $v['day'];
                    unset($v['day']);
                }
            }

            $res = M('statistical_order_day')->addAll($res);

            if($res === false){
                throw new Exception('添加按天订单数量统计出错！');
            }
            $log_order = ['name'=>'订单按天订单数量统计','log_status'=>'1','run_msg'=>'正常完成','run_time'=>time()];
            M('statistical_log')->add($log_order);


        }catch (Exception $e){
            $log_order = ['name'=>'订单按天订单数量统计','log_status'=>'2','run_msg'=>$e->getMessage(),'run_time'=>time()];
            M('statistical_log')->add($log_order);
        }


    }

    //订单按天，统计商品销售数量
    public function goodsSaleList(){


        $time1 = date("Y-m-d",strtotime("-1 day"))." 23:59:59";//昨天凌晨结束
        $time = strtotime($time1);
        $time_start2 = date("Y-m-d",strtotime("-1 day"))." 0:0:0";//昨天开始
        $time_start = strtotime($time_start2);

        try{
            //查询是否已经插入过昨天订单统计记录
            $is_add = M('statistical_sales_goods')->where(['time'=>$time_start2])->find();
            if(!empty($is_add)){
                $dele_res = M('statistical_sales_goods')->where(['time'=>$time_start2])->delete();
                if($dele_res === false){
                    throw new Exception('已经添加昨日统计商品销售数量的统计记录,删除上次记录失败');
                }

            }

            $rest =  M('statistical_sales_goods')->select();
            if(!empty($rest)){
                $where = [
                    ['a.bill_type' => 'payments'],
                    ['a.transaction_id' => ['neq', '']],
                    ['a.time' => ['EGT', $time_start]],
                    ['a.time' => ['lt', $time]],
                ];
            }else{

                $where = [
                    ['a.bill_type' => 'payments'],
                    ['a.transaction_id' => ['neq', '']],
                    ['a.time' => ['lt', $time]],
                ];

            }



            $field = 'a.`time`,a.`local_id`,b.`goods_id`,b.`product_id`, b.`name`, b.`bn`,b.`nums`, b.`amount`, b.`store_pid`, FROM_UNIXTIME(a.`time`,"%Y-%m-%d") as day,p.barcode';

            $list = M('b2c_bills')
                ->alias('a')
                ->join(C('DB_PREFIX').'b2c_order_items b on a.order_id=b.order_id', 'LEFT')
                ->join(C('DB_PREFIX').'b2c_products p on p.goods_id=b.goods_id and p.product_id=b.product_id', 'LEFT')
                ->field($field)
                ->where($where)
                ->order('a.time desc')
                ->select();

            if(!empty($list)){
                foreach ($list as &$v){
                    $v['created_at'] = date('Y-m-d H:i:s',time());
                    $v['goods_name'] = $v['name'];
                    $v['goods_num'] = $v['nums'];
                    $v['time'] = $v['day'];
                    $v['money'] = $v['amount'];
                    unset($v['day']);
                    unset($v['name']);
                    unset($v['nums']);
                    unset($v['amount']);
                }
            }

            $res = M('statistical_sales_goods')->addAll($list);
            if($res === false){
                throw new Exception('添加按天订单销售商品出错！');
            }

            $log_order_goods = ['name'=>'订单销售商品','log_status'=>'1','run_msg'=>'正常完成','run_time'=>time()];
            M('statistical_log')->add($log_order_goods);

        }catch (Exception $e){
            $log_order_goods = ['name'=>'订单销售商品','log_status'=>'2','run_msg'=>$e->getMessage(),'run_time'=>time()];
            M('statistical_log')->add($log_order_goods);
        }

    }


    /**
     * 退款统计
     */
    public function orderRefund()
    {
        $addModel = M();
        //开启事物
        $addModel->startTrans();
        try{
            $refundModel = M('statistical_refund');
            $count = $refundModel->count('id');
            $map = [
                'b.bill_type' =>'refunds',
                'b.refund_id' => ['neq', 0]
            ];
            if ($count != 0){
                $endTime = strtotime(date('Y-m-d'));
                $beginTime = $endTime - 86400;
                //判断当天是否已执行
                $isTodayRun = M('statistical_refund')->where(['UNIX_TIMESTAMP(time)' => [['egt', $beginTime], ['lt', $endTime]]])->count('id');
                if ($isTodayRun != 0){
                    //删除当天执行的数据
                    M('statistical_refund_orders')->where(['UNIX_TIMESTAMP(time)' => [['egt', $beginTime], ['lt', $endTime]]])->delete();
                    M('statistical_refund')->where(['UNIX_TIMESTAMP(time)' => [['egt', $beginTime], ['lt', $endTime]]])->delete();
                }
                $map['b.time'] = [['egt', $beginTime], ['lt', $endTime]];
            }
            $querySql = M('b2c_refund_money a')
                ->join("INNER JOIN ". C('DB_PREFIX') ."b2c_bills b ON  a.`order_id` = b.`order_id` AND a.`id` = b.`refund_id`")
                ->where($map)
                ->group('b.local_id, a.`order_id`, FROM_UNIXTIME(b.`time`, \'%Y-%m-%d\')')
                ->field(['b.local_id','a.order_id', 'b.time', 'sum(b.price_detail) money'])->select(false);
            $model = new Model();
            $lists = $model->table($querySql.' a')->order('`time` DESC')->group('a.local_id, FROM_UNIXTIME(`time`, \'%Y-%m-%d\')')
                ->field('local_id, time, COUNT(order_id) orderTotal, SUM(money) moneySum')->select();
            //查询错误，抛出异常
            if ($lists === false) throw new Exception('查询失败');

            foreach ($lists as $list){
                $data = [
                    'local_id' => $list['local_id'],
                    'refund_order_num' => $list['orderTotal'],
                    'money' => $list['moneySum'],
                    'time' => date('Y-m-d H:i:s', $list['time']),
                    'created_at' => date('Y-m-d H:i:s')
                ];
                $addModel->table(C('DB_PREFIX').'statistical_refund')->add($data);
                $map['b.local_id'] = $list['local_id'];
                $map['b.time'] = [['egt', strtotime(date('Y-m-d',$list['time']))], ['lt', strtotime(date('Y-m-d',$list['time']))+86400]];
                $details = M('b2c_refund_money a')
                    ->join("INNER JOIN ". C('DB_PREFIX') ."b2c_bills b ON  a.`order_id` = b.`order_id` AND a.`id` = b.`refund_id`")
                    ->where($map)->group('a.order_id')
                    ->field(['a.order_id', 'GROUP_CONCAT(a.type) `type`', 'b.time'])->select();

                if ($details === false) throw new Exception($list['local_id']."查询失败");

                $orders = [];
                foreach ($details as $detail){
                    $order = [
                        'local_id' => $list['local_id'],
                        'order_id' => $detail['order_id'],
                        'refund_type' => $detail['type'],
                        'time' => date('Y-m-d H:i:s', $detail['time']),
                        'created_at' => date('Y-m-d H:i:s')
                    ];
                    array_push($orders, $order);
                }
                $addModel->table(C('DB_PREFIX').'statistical_refund_orders')->addAll($orders);
            }
            //提交数据
            $addModel->commit();
            //执行成功,记录日志
            $logMsg = [
                'name' => '退款统计',
                'log_status' => '1',
                'run_msg' => '',
                'run_time' => time()
            ];
            M('statistical_log')->add($logMsg);
            echo '处理完成';
        }catch (Exception $e){
            //回滚数据
            $addModel->rollback();
            //捕获异常
            $logMsg = [
                'name' => '退款统计',
                'log_status' => '2',
                'run_msg' => $e->getMessage(),
                'run_time' => time()
            ];
            M('statistical_log')->add($logMsg);
            echo '执行错误';
        }
    }

    /**
     * 商品销售分类
     */
    public function sellCat()
    {
        try{
            $map = [
                'a.pay_status' => '1',
                'b.bill_type' => 'payments',
                'c.item_type' => ['in','product, pkg']
            ];
            $goodsCateModel = M('statistical_goods_cate');
            $count = $goodsCateModel->count('id');
            if ($count != 0){
                $endTime = strtotime(date('Y-m-d'));
                $beginTime = $endTime - 86400;
                //判断当天是否已执行
                $isTodayRun = M('statistical_goods_cate')->where(['UNIX_TIMESTAMP(time)' => [['egt', $beginTime], ['lt', $endTime]]])->count('id');
                if ($isTodayRun != 0){
                    //删除当天执行的数据
                    M('statistical_goods_cate')->where(['UNIX_TIMESTAMP(time)' => [['egt', $beginTime], ['lt', $endTime]]])->delete();
                }
                $map['b.time'] = [['egt', $beginTime], ['lt', $endTime]];
            }

            $lists = M('b2c_orders a')
                ->join("INNER JOIN ". C('DB_PREFIX') ."b2c_bills b ON  a.`order_id` = b.`order_id`")
                ->join("INNER JOIN ". C('DB_PREFIX') ."b2c_order_items c ON  a.`order_id` = c.`order_id`")
                ->join("INNER JOIN ". C('DB_PREFIX') ."b2c_store_product d ON  c.`store_pid` = d.`store_pid` AND d.`local_id` = b.`local_id`")
                ->join("INNER JOIN ". C('DB_PREFIX') ."b2c_store_cat e ON  d.`scate_id` = e.`store_cat_id`")
                ->where($map)->group('a.local_id, d.`scate_id`, FROM_UNIXTIME(b.`time`, \'%Y-%m-%d\')')
                ->field(['a.local_id, b.`time`', 'd.`scate_id`', 'e.`cat_name`', 'SUM(c.`amount`) amount', 'SUM(c.`nums`) nums'])->select();
            if ($lists === false){
                throw new Exception('查询失败');
            }
            $categorys = [];
            foreach ($lists as $list){
                $category = [
                    'local_id' => $list['local_id'],
                    'goods_cate_id' => $list['scate_id'],
                    'name' => $list['cat_name'],
                    'num' => $list['nums'],
                    'money' => $list['amount'],
                    'time' => date('Y-m-d H:i:s', $list['time']),
                    'created_at' => date('Y-m-d H:i:s'),
                ];
                array_push($categorys, $category);
            }
            if ($goodsCateModel->addAll($categorys) === false) throw new Exception('插入失败');

            //执行成功,记录日志
            $logMsg = [
                'name' => '商品销售分类',
                'log_status' => '1',
                'run_msg' => '',
                'run_time' => time()
            ];
            M('statistical_log')->add($logMsg);
            echo '执行成功';
        }catch (Exception $e){
            //捕获异常
            $logMsg = [
                'name' => '商品销售分类',
                'log_status' => '2',
                'run_msg' => $e->getMessage(),
                'run_time' => time()
            ];
            M('statistical_log')->add($logMsg);
            echo '执行错误';
        }
    }

}